﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace PDFGenerator
{
    class NamePair
    {
        private string productName;
        private string measureName;

        public NamePair(string productName, string measureName)
        {
            this.productName = productName;
            this.measureName = measureName;
        }

        public string MeasureName
        {
            get
            {
                return this.measureName;
            }
        }

        public string ProductName
        {
            get
            {
                return this.productName;
            }
        }

    }
    class SQLReader
    {
        private MySqlConnection connection;

        public SQLReader(string host, string database, string user, string password)
        {
            string connectionString = "Data Source=" + host + ";Database=" + database + ";User ID=" + user + ";Password=" + password;
            connection = new MySqlConnection(connectionString);
        }

        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        Console.WriteLine("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        Console.WriteLine("Invalid username/password, please try again");
                        break;
                }
                return false;
            }
        }

        
        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        public Dictionary<int, NamePair> GetData()
        {
            Dictionary<int, NamePair> result = new Dictionary<int, NamePair>();
            string query = "select p.ProductId, p.Name, m.Name " +
                            "from products p join measures m" +
                            " on p.MeasureId = m.MeasureId";
            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    int productId = dataReader.GetInt32(0);
                    string productName = dataReader.GetString(1);
                    string measureName = dataReader.GetString(2);
                    result.Add(productId, new NamePair(productName, measureName));
                }

                dataReader.Close();

                this.CloseConnection();

                return result;
            }
            else
            {
                return result;
            }
        }
    }
}
